package com.readbook.util;

import java.io.*;
import java.text.SimpleDateFormat;
import java.util.*;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import com.readbook.service.WxsycService;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;


public class ExcelUtils {

    public static String EXCEL2003 = "EXCEL2003";
    public static String EXCEL2007 = "EXCEL2007";
    public static String EXCEL2010 = "EXCEL2010";


    /**
     * 根据创建好的workbook对象，向响应端输出EXCEL
     *
     * @param workbook
     * @param exportFileName
     * @param response
     * @throws Exception
     */
    public static void exportExcelData(Workbook workbook,
                                       String exportFileName, HttpServletResponse response)
            throws Exception {
        response.reset();
        StringBuffer headerSB = new StringBuffer();
        headerSB.append("attachment; filename=");
        headerSB.append(exportFileName);
        response.setHeader("Content-Disposition", new String(headerSB
                .toString().getBytes("GBK"), "ISO-8859-1"));
        response.setContentType("application/vnd.ms-excel");
        response.setHeader("Content-Transfer-Encoding", "binary");
        response.setHeader("Cache-Control",
                "must-revalidate, post-check=0, pre-check=0");
        response.setHeader("Pragma", "public");
        // 获得输出流
        OutputStream out = null;
        // 输出
        try {
            out = response.getOutputStream();
            workbook.write(out);
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (out != null) {
                out.close();
            }
        }
    }


    public static String getHSSFCellValue(HSSFCell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    //BigDecimal db = new BigDecimal(cell.getNumericCellValue()).setScale(4,BigDecimal.ROUND_HALF_DOWN);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    Date date = cell.getDateCellValue();
                    return sdf.format(date);
					/*java.text.DecimalFormat  df=new   java.text.DecimalFormat("#.####");
					return df.format(cell.getNumericCellValue());*/
                case HSSFCell.CELL_TYPE_STRING:
                    return String.valueOf(cell.getStringCellValue()).replaceAll("\\s", "");
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue()).replaceAll("\\s", "");
                case HSSFCell.CELL_TYPE_FORMULA:
                    return String.valueOf(cell.getCellFormula()).replaceAll("\\s", "");
                default:
                    break;
            }
        }
        return "";
    }


    public static String getXSSFCellValue(XSSFCell cell) {
        if (cell != null) {
            switch (cell.getCellType()) {
                case XSSFCell.CELL_TYPE_NUMERIC:
                    java.text.DecimalFormat df = new java.text.DecimalFormat("#.####");
                    return df.format(cell.getNumericCellValue());
                case XSSFCell.CELL_TYPE_STRING:
                    return String.valueOf(cell.getStringCellValue()).replaceAll("\\s", "");
                case XSSFCell.CELL_TYPE_BOOLEAN:
                    return String.valueOf(cell.getBooleanCellValue()).replaceAll("\\s", "");
                case XSSFCell.CELL_TYPE_FORMULA:
                    return String.valueOf(cell.getCellFormula()).replaceAll("\\s", "");
                default:
                    break;
            }
            return "";
        } else {
            return "";
        }
    }


    //获取数据
    public static List<Map<String, Object>> getExceldatas(HttpServletRequest request) throws IOException {
        List<Map<String, Object>> listdata = new ArrayList<>();
        String filename = request.getParameter("filename").toString();
        String filepath = request.getParameter("filepath").toString();
        String realpath = request.getRealPath(filepath);

        //后去文件路径与文件后缀
        String suffix = filename.substring(filename.lastIndexOf(".") + 1);
        //获取文件流
        File srcFile = new File(realpath);
        //创建文件字节输入流对象，并接在源上
        InputStream is = new FileInputStream(srcFile);
        //获取表头
        String row_head = "";
        String[] columns_arr = null;

        if (suffix.equals("xls")) {
            HSSFWorkbook wbs = new HSSFWorkbook(is);
            //获取第一个单元格
            HSSFSheet childSheet = wbs.getSheetAt(0);
            //获取第一行 第一行为表头
            HSSFRow rowone = childSheet.getRow(0);
            if (null != rowone) {
                for (int k = 0; k < rowone.getLastCellNum(); k++) {
                    HSSFCell cell = rowone.getCell(k);
                    if (null != cell) {
                        if (k > 0) {
                            row_head += ",";
                            row_head += ExcelUtils.getHSSFCellValue(cell);
                        } else {
                            row_head += ExcelUtils.getHSSFCellValue(cell);
                        }
                    }
                }
            }

            if (row_head.trim().equals("")) {
                return listdata;
            }
            columns_arr = row_head.split(",");
            //数据从第二行开始
            for (int j = 1; j <= childSheet.getLastRowNum(); j++) {
                HSSFRow row = childSheet.getRow(j);
                StringBuilder row_value = new StringBuilder();
                if (null != row) {
                    Map<String, Object> data_row = new HashMap<String, Object>();
                    for (int k = 0; k < columns_arr.length; k++) {
                        HSSFCell cell = row.getCell(k);
                        if (cell != null) {
                            int celltype = cell.getCellType();
                            String keys = columns_arr[k];
                            String values = "";
                            if (celltype == 3) {
                                continue;
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                values = ExcelUtils.getHSSFCellValue(cell);
                            }
                            data_row.put(keys, values);
                            row_value.append(values);
                        }
                    }
                    if (row_value.length() > 0) {
                        listdata.add(data_row);
                        row_value.delete(0, row_value.length() - 1);
                    }
                }
            }
        } else {
            XSSFWorkbook wbs = new XSSFWorkbook(is);
            //第一个工作表
            XSSFSheet childSheet = wbs.getSheetAt(0);
            //第一行
            XSSFRow rowone = childSheet.getRow(0);
            if (null != rowone) {
                for (int k = 0; k < rowone.getLastCellNum(); k++) {
                    XSSFCell cell = rowone.getCell(k);
                    if (null != cell) {
                        int celltype = cell.getCellType();
                        if (celltype == 3) {
                            continue;
                        }
                        if (k > 0)
                            row_head += ",";
                        row_head += ExcelUtils.getXSSFCellValue(cell);
                    } else {
                        row_head += ExcelUtils.getXSSFCellValue(cell);
                    }
                }
            }
            if (row_head.trim().equals("")) {
                return listdata;
            }
            columns_arr = row_head.split(",");
            //数据从第二行开始
            for (int j = 1; j <= childSheet.getLastRowNum(); j++) {
                XSSFRow row = childSheet.getRow(j);
                StringBuilder row_value = new StringBuilder();
                if (null != row) {
                    Map<String, Object> data_row = new HashMap<String, Object>();
                    for (int k = 0; k < columns_arr.length; k++) {
                        XSSFCell cell = row.getCell(k);
                        if (cell != null) {
                            int celltype = cell.getCellType();
                            String keys = columns_arr[k];
                            String values = "";
                            if (celltype == 3) {
                                continue;
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                values = ExcelUtils.getXSSFCellValue(cell);
                            }
                            data_row.put(keys, values);
                            row_value.append(values);
                        }
                    }
                    if (row_value.length() > 0) {
                        listdata.add(data_row);
                        row_value.delete(0, row_value.length() - 1);
                    }
                }
            }
        }
        return listdata;
    }



    //获取数据
    public static List<Map<String, Object>> getLocationExceldatas() throws IOException {
        List<Map<String, Object>> listdata = new ArrayList<>();

        String filePath ="C://Users/mmy/Desktop/新建文件夹/ERROR（改）.xlsx";


        //后去文件路径与文件后缀
        String suffix = filePath.substring(filePath.lastIndexOf(".") + 1);
        //获取文件流
        File srcFile = new File(filePath);
        //创建文件字节输入流对象，并接在源上
        InputStream is = new FileInputStream(srcFile);
        //获取表头
        String row_head = "";
        String[] columns_arr = null;

        if (suffix.equals("xls")) {
            HSSFWorkbook wbs = new HSSFWorkbook(is);
            //获取第一个单元格
            HSSFSheet childSheet = wbs.getSheetAt(0);
            //获取第一行 第一行为表头
            HSSFRow rowone = childSheet.getRow(0);
            if (null != rowone) {
                for (int k = 0; k < rowone.getLastCellNum(); k++) {
                    HSSFCell cell = rowone.getCell(k);
                    if (null != cell) {
                        if (k > 0) {
                            row_head += ",";
                            row_head += ExcelUtils.getHSSFCellValue(cell);
                        } else {
                            row_head += ExcelUtils.getHSSFCellValue(cell);
                        }
                    }
                }
            }

            if (row_head.trim().equals("")) {
                return listdata;
            }
            columns_arr = row_head.split(",");
            //数据从第二行开始
            for (int j = 1; j <= childSheet.getLastRowNum(); j++) {
                HSSFRow row = childSheet.getRow(j);
                StringBuilder row_value = new StringBuilder();
                if (null != row) {
                    Map<String, Object> data_row = new HashMap<String, Object>();
                    for (int k = 0; k < columns_arr.length; k++) {
                        HSSFCell cell = row.getCell(k);
                        if (cell != null) {
                            int celltype = cell.getCellType();
                            String keys = columns_arr[k];
                            String values = "";
                            if (celltype == 3) {
                                continue;
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                values = ExcelUtils.getHSSFCellValue(cell);
                            }
                            data_row.put(keys, values);
                            row_value.append(values);
                        }
                    }
                    if (row_value.length() > 0) {
                        listdata.add(data_row);
                        row_value.delete(0, row_value.length() - 1);
                    }
                }
            }
        } else {
            XSSFWorkbook wbs = new XSSFWorkbook(is);
            //第一个工作表
            XSSFSheet childSheet = wbs.getSheetAt(0);
            //第一行
            XSSFRow rowone = childSheet.getRow(0);
            if (null != rowone) {
                for (int k = 0; k < rowone.getLastCellNum(); k++) {
                    XSSFCell cell = rowone.getCell(k);
                    if (null != cell) {
                        int celltype = cell.getCellType();
                        if (celltype == 3) {
                            continue;
                        }
                        if (k > 0)
                            row_head += ",";
                        row_head += ExcelUtils.getXSSFCellValue(cell);
                    } else {
                        row_head += ExcelUtils.getXSSFCellValue(cell);
                    }
                }
            }
            if (row_head.trim().equals("")) {
                return listdata;
            }
            columns_arr = row_head.split(",");
            //数据从第二行开始
            for (int j = 1; j <= childSheet.getLastRowNum(); j++) {
                XSSFRow row = childSheet.getRow(j);
                StringBuilder row_value = new StringBuilder();
                if (null != row) {
                    Map<String, Object> data_row = new HashMap<String, Object>();
                    for (int k = 0; k < columns_arr.length; k++) {
                        XSSFCell cell = row.getCell(k);
                        if (cell != null) {
                            int celltype = cell.getCellType();
                            String keys = columns_arr[k];
                            String values = "";
                            if (celltype == 3) {
                                continue;
                            } else {
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                values = ExcelUtils.getXSSFCellValue(cell);
                            }
                            data_row.put(keys, values);
                            row_value.append(values);
                        }
                    }
                    if (row_value.length() > 0) {
                        listdata.add(data_row);
                        row_value.delete(0, row_value.length() - 1);
                    }
                }
            }
        }
        return listdata;
    }


    public static void insertNewStudent() throws IOException {
        /*WxsycService wxsycService = (WxsycService) SpringContextUtil.getBean("wxsycService");
        List<Map<String, Object>> list1=getLocationExceldatas();
        List<Map<String, Object>> list2=new ArrayList<>();*/
        List<Map<String, Object>> list1=getLocationExceldatas();
        String errstr="";
        for(int i=0;i<list1.size();i++){
            Map<String,Object>maps=new HashMap<>();
            maps=list1.get(i);
            if(maps.get("身份证号")!=null && maps.get("姓名")!=null && maps.get("班主任")!=null && maps.get("班级")!=null){
                Map<String,Object>map1=new HashMap<>();
                map1.put("ID",maps.get("身份证号"));
                map1.put("SFZH",maps.get("身份证号"));
                map1.put("NAME",maps.get("姓名"));
                map1.put("BZR",maps.get("班主任"));
                map1.put("BJ",maps.get("班级"));
                //wxsycService.addNewStudent(map1);
                //list2.add(map1);
            }
        }
    }





    //获取数据
    public static List<Map<String,Object>>getExcelUaccounts(HttpServletRequest request) throws IOException {
        List<Map<String,Object>>listdata=new ArrayList<>();
        String filename=request.getParameter("filename").toString();
        String filepath=request.getParameter("filepath").toString();
        String realpath=request.getRealPath(filepath);

        //后去文件路径与文件后缀
        String suffix = filename.substring(filename.lastIndexOf(".") + 1);
        //获取文件流
        File srcFile = new File(realpath);
        //创建文件字节输入流对象，并接在源上
        InputStream is = new FileInputStream(srcFile);
        //获取表头
        String row_head="";
        String[] columns_arr =null;

        if (suffix.equals("xls")) {
            HSSFWorkbook wbs = new HSSFWorkbook(is);
            //获取第一个单元格
            HSSFSheet childSheet = wbs.getSheetAt(0);
            //获取第一行 第一行为表头
            HSSFRow rowone = childSheet.getRow(0);
            if (null != rowone) {
                for (int k = 0; k < rowone.getLastCellNum(); k++) {
                    HSSFCell cell = rowone.getCell(k);
                    if (null != cell) {
                        if (k > 0) {
                            row_head += ",";
                            row_head += ExcelUtils.getHSSFCellValue(cell);
                        } else {
                            row_head += ExcelUtils.getHSSFCellValue(cell);
                        }
                    }
                }
            }

            if(row_head.trim().equals("")){
                return listdata;
            }
            columns_arr = row_head.split(",");
            //数据从第二行开始
            for(int j=1;j<=childSheet.getLastRowNum();j++){
                HSSFRow row=childSheet.getRow(j);
                StringBuilder row_value=new StringBuilder();
                if(null != row){
                    Map<String, Object>data_row=new HashMap<String,Object>();
                    for(int k = 0;k<columns_arr.length;k++){
                        HSSFCell cell = row.getCell(k);
                        if(cell != null){
                            int celltype=cell.getCellType();
                            String keys=columns_arr[k];
                            String values="";
                            if(celltype==3){
                                continue;
                            }else{
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                values=ExcelUtils.getHSSFCellValue(cell);
                            }
                            data_row.put(keys,values);
                            row_value.append(values);
                        }
                    }
                    if(row_value.length() > 0){
                        listdata.add(data_row);
                        row_value.delete(0, row_value.length()-1);
                    }
                }
            }
        } else {
            XSSFWorkbook wbs = new XSSFWorkbook(is);
            //第一个工作表
            XSSFSheet childSheet = wbs.getSheetAt(0);
            //第一行
            XSSFRow rowone = childSheet.getRow(0);
            if (null != rowone) {
                for (int k = 0; k < rowone.getLastCellNum(); k++) {
                    XSSFCell cell = rowone.getCell(k);
                    if (null != cell) {
                        int celltype=cell.getCellType();
                        if(celltype==3){
                            continue;
                        }
                        if (k > 0)
                            row_head += ",";
                        row_head += ExcelUtils.getXSSFCellValue(cell);
                    } else {
                        row_head += ExcelUtils.getXSSFCellValue(cell);
                    }
                }
            }
            if(row_head.trim().equals("")){
                return listdata;
            }
            columns_arr = row_head.split(",");
            //数据从第二行开始
            for(int j=1;j<=childSheet.getLastRowNum();j++){
                XSSFRow row=childSheet.getRow(j);
                StringBuilder row_value=new StringBuilder();
                if(null != row){
                    Map<String, Object>data_row=new HashMap<String,Object>();
                    for(int k = 0;k<columns_arr.length;k++){
                        XSSFCell cell = row.getCell(k);
                        if(cell != null){
                            int celltype=cell.getCellType();
                            String keys=columns_arr[k];
                            String values="";
                            if(celltype==3){
                                continue;
                            }else{
                                cell.setCellType(cell.CELL_TYPE_STRING);
                                values=ExcelUtils.getXSSFCellValue(cell);
                            }
                            data_row.put(keys,values);
                            row_value.append(values);
                        }
                    }
                    if(row_value.length() > 0){
                        listdata.add(data_row);
                        row_value.delete(0, row_value.length()-1);
                    }
                }
            }
        }
        return listdata;
    }

    public static void main(String args[]) throws Exception {

        /*File f = new File("C:\\Users\\Sonic\\AppData\\Local\\Temp\\tomca-docbase.7339093254572283926.8080\\upload\\20190516162808.xls");
        InputStream in = new FileInputStream(f);
        byte b[] = new byte[(int) f.length()];     //创建合适文件大小的数组
        in.read(b);    //读取文件中的内容到b[]数组
        in.close();
        System.out.println(new String(b));*/
        insertNewStudent();

    }


}
